/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.CES.DAOImplementation;

import com.bionic.CES.DAO.IExecutedqueriesDAO;
import com.bionic.CES.beans.User;
import com.bionic.CES.beans.Executedqueries;
import com.bionic.CES.beans.Query;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLExecutedqueriesDAO extends MYSQLDAO implements IExecutedqueriesDAO{
 @Override
    public void add(Executedqueries executedqueries) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO executedqueries "
                    + "(`Query`, `ExecutedByUser`, `Rate`, `ClosedDate`, `ExecutionMethod`) "
                    + "VALUES(?,?,?,?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
          
            int query_id = executedqueries.getQuery();
            Query query = new Query();
            query.setQueryId(query_id);
            boolean b = new MySQLQueryDAO().findByDynamicSelect(query).isEmpty();
            if (!b) {
                ptmt.setInt(1, executedqueries.getQuery());
            } else {
                throw new SQLException("Such Query with id=" + executedqueries.getQuery() + " doesnt exists");
            }
            int id = executedqueries.getExecutedByUser();
            User user = new User();
            user.setUserid(id);
            b = new MySQLUserDAO().findByDynamicSelect(user).isEmpty();
            if (!b) {
                ptmt.setInt(2, executedqueries.getExecutedByUser());
            } else {
                throw new SQLException("Such User with id=" + executedqueries.getExecutedByUser() + " doesnt exists");
            }
            ptmt.setDouble(3, executedqueries.getRate());
            ptmt.setDate(4, executedqueries.getClosedDate());
            ptmt.setString(5, executedqueries.getExecutionMethod());
            
            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data Added Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(Executedqueries executedqueries) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE executedqueries SET `Query`=?, `ExecutedByUser`=?, `Rate`=?, "
                    + "`ClosedDate`=?, `ExecutionMethod`=? WHERE `id`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            
            int query_id = executedqueries.getQuery();
            Query query = new Query();
            query.setQueryId(query_id);
            boolean b = new MySQLQueryDAO().findByDynamicSelect(query).isEmpty();
            if (!b) {
                ptmt.setInt(1, executedqueries.getQuery());
            } else {
                throw new SQLException("Such Query with id=" + executedqueries.getQuery() + " doesnt exists");
            }
            int id = executedqueries.getExecutedByUser();
            User user = new User();
            user.setUserid(id);
            b = new MySQLUserDAO().findByDynamicSelect(user).isEmpty();
            if (!b) {
                ptmt.setInt(2, executedqueries.getExecutedByUser());
            } else {
                throw new SQLException("Such User with id=" + executedqueries.getExecutedByUser() + " doesnt exists");
            }
            ptmt.setDouble(3, executedqueries.getRate());
            ptmt.setDate(4, executedqueries.getClosedDate());
            ptmt.setString(5, executedqueries.getExecutionMethod());

            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data Updated Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteById(int id) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM executedqueries WHERE `id`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, id);
            ptmt.executeUpdate();
            connection.commit();
            System.out.println("Data deleted Successfully");
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Executedqueries> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<Executedqueries> result = new ArrayList();
            String queryString = "SELECT * FROM executedqueries";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Executedqueries executedqueries = new Executedqueries();
                executedqueries.setId(resultSet.getInt("id"));
                executedqueries.setQuery(resultSet.getInt("Query"));
                executedqueries.setExecutedByUser(resultSet.getInt("ExecutedByUser"));
                executedqueries.setRate(resultSet.getDouble("Rate"));
                executedqueries.setClosedDate(resultSet.getDate("ClosedDate"));
                executedqueries.setExecutionMethod(resultSet.getString("ExecutionMethod"));
                result.add(executedqueries);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Executedqueries> findByDynamicSelect(Executedqueries executedqueries) throws SQLException {
        connection = getConnection();
        try {
            List<Executedqueries> result = new ArrayList();
            String queryString = "SELECT * FROM executedqueries WHERE ";
            int i = 0;
            if (executedqueries.getId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`id`=" + executedqueries.getId();
                i++;
            }
            if (executedqueries.getQuery() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Query`=" + executedqueries.getQuery();
                i++;
            }
            if (executedqueries.getExecutedByUser() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`ExecutedByUser`=" + executedqueries.getExecutedByUser();
                i++;
            }
            if (executedqueries.getRate() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Rate`=" + executedqueries.getRate();
                i++;
            }
            if (executedqueries.getClosedDate() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`ClosedDate`=\"" + executedqueries.getClosedDate() + "\"";
                i++;
            }
            if (executedqueries.getExecutionMethod() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`ExecutionMethod`=\"" + executedqueries.getExecutionMethod() + "\"";
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                executedqueries = new Executedqueries();
                executedqueries.setId(resultSet.getInt("id"));
                executedqueries.setQuery(resultSet.getInt("Query"));
                executedqueries.setExecutedByUser(resultSet.getInt("ExecutedByUser"));
                executedqueries.setRate(resultSet.getDouble("Rate"));
                executedqueries.setClosedDate(resultSet.getDate("ClosedDate"));
                executedqueries.setExecutionMethod(resultSet.getString("ExecutionMethod"));
                result.add(executedqueries);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }   
}
